#-*-coding:utf-8-*-
'''
Created on 2011-1-16
SQLAlchemy Metadata and Session object
@author: zhaoyh
'''
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from config import engine
__all__ = ['Base', 'DBSession', 'metadata','PageList','do_','getsession',]

# SQLAlchemy session manager. 
# DBSession() returns the session object appropriate for the current request.
maker = sessionmaker(autoflush=True)
DBSession = scoped_session(maker)
metadata = MetaData()
DBSession.configure(bind=engine)
metadata.bind = engine
# The declarative Base
Base = declarative_base(metadata=metadata)
class ListView(object):
    '''
	列表页面的查询类分页类
	obj:要查询的模型
	page：页面信息，包含
	order=None,limit='20',offset='1',col=None,keywords=None
	 page=web.input(numPerPage=20,pageNum=1,keywords='',orderField='group_code',col=['group_code','group_name'])
       
	'''
    
    def __init__(self,obj,conditions=None,limit=20,offset=0,orderFields=None):
        self.obj=obj
        self.limit=limit#不限制怎么办呢？
        self.offset=offset
        self.orderFields=orderFields        
        self.condition=conditions       
    def list(self): 
        '''
                     返回列表
        TODO:待测试
        '''
        order=self.page.get('orderFields',None)
        limit=self.page.get('numPerPage',20)
        offset=self.page.get('pageNum',1)
        if isinstance(self.obj,object):
            list=DBSession.query(self.obj).filter(self.condition).order_by(orderFields)\
                    [int(limit)*(int(offset)-1):int(limit)*\
                     int(offset)-1+int(limit)]
        else:
            list=DBSession.execute(self.obj)[int(limit)*(int(offset)-1):int(limit)*\
                     int(offset)-1+int(limit)]
        #self.condition=[group_code='1']    
        return list 
    def to_json_result(success, msg='', d=None, **kwargs):
        t = {'success':success, 'message':str(msg), 'data':d}
        t.update(kwargs)
        return json(t)

class PageList(ListView):
    '''
	列表页面的查询类分页类
	obj:要查询的模型
	page：页面信息，包含
	order=None,limit='20',offset='1',col=None,keywords=None
	 page=web.input(numPerPage=20,pageNum=1,keywords='',orderField='group_code',col=['group_code','group_name'])
       
	'''
    
    def __init__(self,obj,page,conditions):
        self.obj=obj
        self.page=page
        self.condition=conditions
        super(PageList,self).__init__()
        
    def __getpage__(self):
        self.order=self.page.get('orderField',None)
        self.limit=self.page.get('numPerPage',20)
        self.offset=self.page.get('pageNum',1)
    def Pageinfo(self,pageNumShown=10):
        '''
                    当前页信息
        '''
        page=self.page
        page.totalCount=DBSession.query(self.obj).filter(self.condition).count()
        page.pageNumShown=pageNumShown
        page.currentPage=self.page['pageNum']
        return page
def makewhere(keywords,col,titles):
    """
    @param keywords:
    @param col:
    @param titles:   
    """
    cd=''
    if keywords!='' and col=='':
        c=[]
        for i in  titles.keys():
            c.append(i+' like \'%'+keywords+'%\'')
        cd=' or '.join(c)
    elif keywords!='' and col!='':
        cd=col+' like \'%'+keywords+'%\''
    return cd
def get_sort_field(model, sort_field='sort', order_name='asc'):

    if request.values.getlist('sort'):
        sort_fields = request.values.getlist('sort')
        order_by = []
        orders = request.values.getlist('order')
        for i, f in enumerate(sort_fields):
            if orders[i] == 'asc':
                order_by.append(model.c[f])
            else:
                order_by.append(model.c[f].desc())
    else:
        order_by = None
        
    return order_by
def do_(query):
    """
    Execute a query
    """
    conn=getsession()
    return conn.execute(query)
def getsession():
    return DBSession()

def create_tables():
    metadata.create_all(engine)

def drop_tables():
    metadata.drop_all(engine)
def autocommit(func, *args, **kwargs):
    """Automatically handle database transactions for decorated controller actions"""
    try:
        result = func(*args, **kwargs)
    except:
        DBSession.rollback()
        raise
    DBSession.commit()
    return result